package per.lee.spingboothelper.db.dao;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

import org.springframework.stereotype.Service;

import lombok.extern.slf4j.Slf4j;
import per.lee.spingboothelper.db.model.ShowSql;
import per.lee.spingboothelper.db.model.SlowSqlModel;

@Slf4j
@Service
public class SlowSqlDao {

	public List<SlowSqlModel> findExplain(List<ShowSql> sqlList) {

		String explain = "explain execute ";

		List<SlowSqlModel> res = new ArrayList<SlowSqlModel>();
		log.info("sql.size = {}", sqlList.size());
		int err = 0;
		for (ShowSql showSql : sqlList) {

			ArrayList<Map<String, Object>> query = BaseDao.query(explain + showSql.getSql());
			log.info("sql.size = {}", query.size());
			for (Map<String, Object> x : query) {
				try {
					SlowSqlModel slowSqlModel = new SlowSqlModel();
					slowSqlModel.setSql(showSql.getSql());
					slowSqlModel.setExecuteTime(showSql.getExecuteTime().toString());
					slowSqlModel.setExtra(x.get("Extra") != null ? x.get("Extra").toString() : "");

					slowSqlModel.setKey(x.get("key") != null ? x.get("key").toString() : "");
					slowSqlModel.setKeyLen(x.get("key_len") != null ? x.get("key_len").toString() : "");

					slowSqlModel
							.setPossibleKeys(x.get("possible_keys") != null ? x.get("possible_keys").toString() : "");
					slowSqlModel.setRef(x.get("ref") != null ? x.get("ref").toString() : "");
					slowSqlModel.setRows(x.get("rows") != null ? x.get("rows").toString() : "");
					slowSqlModel.setSelectType(x.get("select_type") != null ? x.get("select_type").toString() : "");
					slowSqlModel.setType(x.get("type") != null ? x.get("type").toString() : "");

					res.add(slowSqlModel);
				} catch (Exception e) {
					err++;
				}
				if (query.size() > 1) {
					res.add(new SlowSqlModel());
				}
			}
		}
		log.warn("explain err count = {}", err);
		return res;
	}

	public List<ShowSql> findSlowSql(String date) {
		String sql = " SHOW FULL slow WHERE	start_time > '" + date + "' and EXECUTE_TIME> 1500   ";

		ArrayList<Map<String, Object>> query = BaseDao.query(sql);

		List<ShowSql> res = new ArrayList<ShowSql>();
		for (Map<String, Object> x : query) {
			ShowSql showSql = new ShowSql();
			showSql.setSql(x.get("SQL").toString());
			showSql.setExecuteTime(Integer.valueOf(x.get("EXECUTE_TIME").toString()));
			res.add(showSql);
		}

		res = res.stream().filter(x -> !(x.getSql().contains("DROP") || x.getSql().contains("ALTER")
				|| x.getSql().contains("insert") || x.getSql().contains("show"))).collect(Collectors.toList());

		return distinct(res);
	}

	public static List<ShowSql> distinct(List<ShowSql> list) {
		Map<String, ShowSql> sqlMap = new HashMap<String, ShowSql>();
		list.forEach(x -> {
			String key = findKey(x.getSql());
			sqlMap.put(key, x);
		});
		return sqlMap.values().stream().collect(Collectors.toList());
	}

	public static String findKey(String s) {
		if (s.contains("where")) {
			return s.substring(0, s.indexOf("where"));
		}
		return s;
	}

}
